﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SPort
{
    public class SqlHelper
    {

        private static string conncetionStr = ConfigurationManager.AppSettings["sqlConnection"];

        public static List<SerialClass> GetList(string begTime, string endTime)
        {
            var list = new List<SerialClass>();
            var sql = $"select * from DataAnalysis where Date>='{begTime}' and Date<='{endTime}'";

            using (var conn = new SqlConnection(conncetionStr))
            {
                try
                {
                    conn.Open();
                    var command = new SqlCommand(sql, conn);
                    var dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        var date = dr.GetDateTime(0);
                        var model = new SerialClass()
                        {
                            Date = new DateTime(date.Year, date.Month, date.Day, date.Hour, date.Minute, date.Second),
                            DataStr = dr.GetString(1)
                        };
                        list.Add(model);
                    }
                    dr.Close();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }

            return list;
        }

        public static int Add(string datas)
        {
            var i = 0;
            using (var conn = new SqlConnection(conncetionStr))
            {
                try
                {
                    conn.Open();
                    var sql = "insert into DataAnalysis (Date,DataStr) values(@Date,@DataStr)";
                    var comm = new SqlCommand(sql, conn);
                    comm.Parameters.AddRange(new SqlParameter[]{
                        new SqlParameter{ ParameterName="@Date",Value=DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")},
                        new SqlParameter{ ParameterName="@DataStr",Value=datas}
                    });
                    i = comm.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
            return i;
        }
    }
}